
import pymysql
import csv
import random
import json
DISH_CLASS_NUM=15
IGNORE=0
#                   DBZ,ZF,TL,      WA,   WB1,WB2,WC,WE,XIN,  XI, GAI,TIE
STANDARD_NUTRITION=[90,IGNORE,IGNORE,1000,1.5,1.3,60,10,15,   50, 800, 15]
# CLASS:[CLASS_NUM,DAY_REQUIRED_NUM]
RECIPE_DISH_NUM_MAP = {
    1: [10, 1],
    2: [3, 1],
    3: [50, 1],
    4: [42, 1],
    5: [2, 2],
    6: [58, 2],
    7: [14, 1],
    8: [3, 1],
    9: [10, 1],
    10: [159, 7],
    11: [197, 1],
    12: [176, 4],
    13: [161, 2],
    14: [70, 2],
    15: [50, 4],
    16: [10, 0]
}

def validate_solver(standard,solver):
    #for i in range(len(standard))
    assert len(standard)==len(solver),"标准营养含量种类同解的营养含量种类不同"
    state=True
    for i in range(len(standard)):
        state=((standard[i]<=solver[i]) and state)

    return state

def get_csv_data_lsit(filename):
    csv_file = open(filename)  # 打开csv文件
    csv_reader_lines = csv.reader(csv_file)  # 逐行读取csv文件
    date = []  # 创建列表准备接收csv各行数据
    renshu = 0
    for one_line in csv_reader_lines:
        date.append(one_line)  # 将读取的csv分行数据按行存入列表‘date’中
        renshu = renshu + 1  # 统计行数（这里是学生人数）
    return date

def connectdb():
    #print('连接到mysql服务器...')
    # 打开数据库连接
    # 用户名:hp, 密码:Hp12345.,用户名和密码需要改成你自己的mysql用户名和密码，并且要创建数据库TESTDB，并在TESTDB数据库中创建好表Data
    db = pymysql.connect(
        host="127.0.0.1",
        port=3306,
        user="root",
        password="123456",
        database="sys",
        charset="utf8"
    )
    #print("连接成功")
    return db

def create_table_Data(db):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # 如果存在表Sutdent先删除
    cursor.execute("DROP TABLE IF EXISTS Data")
    sql = """
        CREATE TABLE Data (
            CP_ID bigint NOT NULL,
            Class int,
            DBZ double,
            ZF double,
            TL  double,
            WA double,
            WB1 double,
            WB2 double,
            WC double,
            WE double,
            XIN double,
            XI double,
            GAI double,
            TIE double,
            DGC double,         
            PRIMARY KEY (CP_ID)        
             )
            
        """

    # 创建Sutdent表
    cursor.execute(sql)

def updatedb():
    # 使用cursor()方法获取操作游标
    db=connectdb()
    cursor = db.cursor()

    # SQL 更新语句
    sql = "UPDATE `sys`.`Data` SET `JF` = '炒', `COLOR` = '绿' WHERE (`CP_ID` = '101020000007');" % ('003')

    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
    except:
        print('更新数据失败!')
        # 发生错误时回滚
        db.rollback()

def create_table_color(db):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    cursor.execute("DROP TABLE IF EXISTS COLOR")
    sql = """
        CREATE TABLE `LP`.`COLOR` (
  `CP_ID` BIGINT NOT NULL,
  `CP_DL` VARCHAR(45) NULL,
  `CP_XL` VARCHAR(45) NULL,
  `CP_CLASS` VARCHAR(45) NULL,
  `CP_JF` VARCHAR(45) NULL,
  `CP_COLOR` VARCHAR(45) NULL,
  
  PRIMARY KEY (`CP_ID`));


        """

    # 创建Sutdent表
    cursor.execute(sql)

def insertdb_COLOR(db, value_str):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 插入语句

    sql = "INSERT INTO COLOR VALUES %s;" % (value_str)

    print(sql)

    # sql = "INSERT INTO Data VALUES (101020000007,13.0,4.3,21.2,0.1,0.0,2.0,0.2,0.8,1.2,2.2,12.6,1.0,16.0);"

    # sql = "INSERT INTO Data(ID, Name, Grade) \
    #    VALUES ('%s', '%s', '%d')" % \
    #    ('001', 'HP', 60)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("插入成功")
        return True
    except:
        # Rollback in case there is any error
        print('插入数据失败!')
        print(value_str)
        db.rollback()
        return False

def insertdb_Data(db, value_str):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 插入语句

    sql = "INSERT INTO Data VALUES %s;" % (value_str)

    # sql = "INSERT INTO Data VALUES (101020000007,13.0,4.3,21.2,0.1,0.0,2.0,0.2,0.8,1.2,2.2,12.6,1.0,16.0);"

    # sql = "INSERT INTO Data(ID, Name, Grade) \
    #    VALUES ('%s', '%s', '%d')" % \
    #    ('001', 'HP', 60)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        db.commit()
        print("插入成功")
        return True
    except:
        # Rollback in case there is any error
        print('插入数据失败!')
        print(value_str)
        db.rollback()
        return False

def querydb_by_class(db, class_id):
    cursor = db.cursor()
    sql = "SELECT CP_ID,CLASS,DBZ,ZF,TL,WA,WB1,WB2,WC,WE,XIN,XI,GAI,TIE FROM Data where CLASS=%d" % (class_id)
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        closedb(db)
        return results

    except:
        print("Error: unable to fecth data")
        closedb(db)

#查询菜品的荤素类别
def querydb_by_dishid_to_get_class(db, cp_id):
    cursor = db.cursor()
    sql = "SELECT CLASS FROM Data where CP_ID=%d" % (cp_id)
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        #closedb(db)
        return results[0][0]

    except:
        print("Error: unable to fecth data")
        closedb(db)

#查询颜色
def querydb_by_dishid_to_get_color(db, cp_id):
    cursor = db.cursor()
    sql = "SELECT CP_COLOR FROM COLOR where CP_ID=%d" % (cp_id)
    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 获取所有记录列表
        results = cursor.fetchall()
        #closedb(db)
        return results[0][0]

    except:
        print("Error: unable to fecth data")
        closedb(db)

def deletedb(db):
    # 使用cursor()方法获取操作游标
    cursor = db.cursor()

    # SQL 删除语句
    sql = "DELETE FROM Data WHERE Grade = '%d'" % (100)

    try:
        # 执行SQL语句
        cursor.execute(sql)
        # 提交修改
        db.commit()
    except:
        print('删除数据失败!')
        # 发生错误时回滚
        db.rollback()

def closedb(db):
    db.close()

def insert_to_db_data(db,csv_name):
    # 连接MySQL数据库

    #createtable(db)  # 创建表
    data = get_csv_data_lsit(csv_name)
    # j,k分别是插入成功和失败的记录条数
    failed_list=[]
    z = 1
    j = 1
    k = 1
    for i in data:
        str_ = str(tuple(i))
        db = connectdb()  # 连接MySQL数据库
        state = insertdb_Data(db, str_)
        if state == True:
            print("累计插入数据成功%d条" % (j))
            j += 1
            z+=1
        else:
            print("累计插入数据失败%d条" % (j))
            print("第%d条数据插入失败"%(z))
            k += 1
            failed_list.append(z)
            z+=1

    print("第"+str(failed_list)+"插入失败")

    closedb(db)
    return j, k

def insert_to_db_color(db,csv_name):
    # 连接MySQL数据库

    #createtable(db)  # 创建表
    data = get_csv_data_lsit(csv_name)
    # j,k分别是插入成功和失败的记录条数
    failed_list=[]
    z = 1
    j = 1
    k = 1
    for i in data:
        str_ = str(tuple(i))
        db = connectdb()  # 连接MySQL数据库
        state = insertdb_COLOR(db, str_)
        if state == True:
            print("累计插入数据成功%d条" % (j))
            j += 1
            z+=1
        else:
            print("累计插入数据失败%d条" % (j))
            print("第%d条数据插入失败"%(z))
            k += 1
            failed_list.append(z)
            z+=1

    print("第"+str(failed_list)+"插入失败")

    closedb(db)
    return j, k

# int_max 是 querydb_by_class查询结果的记录数,int_len是每日食谱需要该类菜品的数量
def get_random_list(int_len, int_max):
    result = []
    for i in range(int_len):
        result.append(random.randint(1, int_max))
    return result

def get_unrepeated_list(class_id,random_select_list):
    while (len(random_select_list) != len(set(random_select_list))):
        random_select_list = get_random_list(RECIPE_DISH_NUM_MAP[class_id][1], RECIPE_DISH_NUM_MAP[class_id][0])
    return random_select_list

def list_add(a,b):
    c = []
    for i in range(len(a)):
        c.append(a[i]+b[i])
    return c

def format_list(lst):
    for i in range(len(lst)):
        lst[i]=round(lst[i],1)
    return lst

#return random dish_class_id_list and sum of dishes nutrition_lsit.
def query_nutrition_sum_by_class_id(class_id):
    db = connectdb()

    results = querydb_by_class(db, class_id)
    #for i in results:
        #print(i)
    results_length = len(results)
    if results_length == RECIPE_DISH_NUM_MAP[class_id][0]:

        unrepeated_random_select_list = get_unrepeated_list(class_id, get_random_list(RECIPE_DISH_NUM_MAP[class_id][1],
                                                                                      RECIPE_DISH_NUM_MAP[class_id][0]))

        class_id_list = []
        tempt_list = []
        #print("random selected list is:")

        if len(unrepeated_random_select_list)>1:
            for i in unrepeated_random_select_list:
                class_id_list.append(results[i - 1][0])
                #print(results[i - 1])
                if len(tempt_list) == 0:
                    tempt_list = results[i - 1][2:]
                    #print(type(tempt_list))
                else:
                    tempt_list = list_add(tempt_list, results[i - 1][2:])

            sum_list_nutrition = format_list(tempt_list)
        else:

            for i in unrepeated_random_select_list:
            #print("ssss")
            #print(results[i - 1][0])
                class_id_list.append(results[i - 1][0])
                sum_list_nutrition=list(results[i - 1][2:])

        return sum_list_nutrition,class_id_list
    else:
        print("查询得符合条件的数据库记录数：%d"%len(results))
        print("数据库查询结果记录数不一致，请检查数据库或者原始文件！")
        print("class_id:%d"%class_id)
        #print(sum_list_nutrition)
        #print(class_id_list)

def get_single_random_solver(dish_class_num):

    sum_list_nutrition, class_id_list=[],[]
    #print("dish_num:",dish_class_num)
    for i in range(dish_class_num):
        #print(i)
        class_id = i + 1
        if len(sum_list_nutrition)==0:

            sub_sum_list_nutrition, sub_class_id_list=query_nutrition_sum_by_class_id(class_id)
            sum_list_nutrition=sub_sum_list_nutrition
            class_id_list=sub_class_id_list

        else:
            sub_sum_list_nutrition, sub_class_id_list = query_nutrition_sum_by_class_id(class_id)
            #sum_list_nutrition = sub_sum_list_nutrition
            class_id_list += sub_class_id_list
            sum_list_nutrition=list_add(sub_sum_list_nutrition,sum_list_nutrition)
            #print()


    sum_list_nutrition_format=format_list(sum_list_nutrition)
    # print(sum_list_nutrition_format)
    # print(class_id_list)
    #
    # print("len(class_id_list):%d"%len(class_id_list))
    # print(len(set(class_id_list)))
    return class_id_list ,sum_list_nutrition_format

# 蛋白质：能量 =1：4
# 脂肪 ：能量  =1：9
# 糖类 ：能量  =1：4
#蛋白质12%-15%，脂肪20%-30%，碳水化合物55%-65%
def validate_percent(sum_list_nutrition):
#=[219.5, 121.2, 798.9]
    DBZ=sum_list_nutrition[0]
    ZF =sum_list_nutrition[1]
    TL =sum_list_nutrition[2]
    DBZ_percent=DBZ*4/(DBZ*4+ZF*9+TL*4)
    ZF_percent=ZF*9/(DBZ*4+ZF*9+TL*4)
    TL_percent=TL*4/(DBZ*4+ZF*9+TL*4)
    #print(DBZ_percent,ZF_percent,TL_percent)
    result=(DBZ_percent>=0.12 and DBZ_percent<=0.15) and (ZF_percent>=0.2 and ZF_percent<=0.3) and (TL_percent>=0.55 and TL_percent<=0.65)
    return result

def validate_solver_nutrition(standard,solver):
    #for i in range(len(standard))
    assert len(standard)==len(solver),"标准营养含量种类同解的营养含量种类不同"
    state=True
    sum_list_nutrition=solver[:3]
    for i in range(len(standard)):
        state=((standard[i]<=solver[i]) and state and validate_percent(sum_list_nutrition))

    return state

def get_specific_num_of_solver(num):
    solver = []
    #i = 0
    epoch=0
    true_num = 0
    while (true_num < num):
        epoch+=1
        class_id_list, sum_list_nutrition = get_single_random_solver(DISH_CLASS_NUM)

        # print(sum_list_nutrition)

        # print(STANDARD_NUTRITION)

        #i += 1
        if validate_solver_nutrition(STANDARD_NUTRITION, sum_list_nutrition) :
            true_num += 1
            solver.append(class_id_list)
            print("computing the NO.%d 's solver"%(true_num))

    return solver,epoch

def transform_result(db,sub_solver):


    result_standard = {

        "D1": [],
        "D2": [],
        "D3": [],
        "D4": [],
        "D5": [],
        "D6": [],
        "D7": [],
        "D8": [],
        "D9": [],
        "D10": [],
        "D11": [],
        "D12": [],
        "D13": [],
        "D14": [],
        "D15": [],

    }

    # result_standard[1].append(1)


    for dish_id in sub_solver:
        dish_class = querydb_by_dishid_to_get_class(db, dish_id)
        dish_class_str="D"+str(dish_class)
        result_standard[dish_class_str].append(dish_id)

    return result_standard

def get_specific_num_of_solver_as_standard_format(num):

    solver_all=[]

    db = connectdb()

    solver_list, epoch = get_specific_num_of_solver(num)

    solver_list = filter_color(solver_list)

    #print(solver)

    for sub_solver in solver_list:

        solver_all.append(transform_result(db, sub_solver))


    return transform_solver_to_json(solver_list)

def filter_color(solver_list):
    db = connectdb()
    solver_list_copy=solver_list[:]
    num=0
    for solver in solver_list_copy:
        color_set = []
        for id in solver:

            color = querydb_by_dishid_to_get_color(db, id)
            if color not in color_set:
                color_set.append(color)

        if len(color_set)!=6:
            num+=1
            solver_list.remove(solver)
    print("不符合颜色约束的解有%d个"%(num))
    return solver_list

def  transform_solver_to_json(a):
    a_dic = {}

    for i in range(len(a)):
        index = "solver" + str(i)
        a_dic[index] = a[i]
    #print(a_dic)

    a_json = json.dumps(a_dic)

    return a_json

def updatedb_color():
    # 使用cursor()方法获取操作游标
    db=connectdb()
    cursor = db.cursor()
    data = get_csv_data_lsit("color.csv")[1:]
    failed_num = 0
    success_num = 0
    for row in data:
        dish_id=row[0]
        dish_jf=row[1]
        dish_color=row[2]

        # SQL 更新语句
        sql = "UPDATE `sys`.`Data` SET `JF` = %s, `COLOR` = %s WHERE (`CP_ID` = %s);" % (dish_jf,dish_color,dish_id)
        print(sql)
        try:
            # 执行SQL语句
            cursor.execute(sql)
            # 提交到数据库执行
            if db.commit():
                success_num=success_num+1
        except:
            print('更新数据失败!')
            # 发生错误时回滚
            failed_num=failed_num+1
            db.rollback()
    return failed_num,success_num

if __name__ == '__main__':

    #initial block
    # create_table_Data(connectdb(Ï))
    # insert_to_db_data(connectdb(),"data_add.csv")
    # updatedb_color()

    #solver block
    db=connectdb()
    solver_dic=get_specific_num_of_solver_as_standard_format(2)
    #
    #print(solver_dic)
    # print(len(solver_dic))


